#!/usr/bin/env node
/* global process */

/**
 * 数据库初始化脚本
 * 用法: node init-db.js
 */

import mysql from 'mysql2/promise';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import dotenv from 'dotenv';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 加载 .env 文件
dotenv.config({ path: path.join(__dirname, '.env') });

async function initDatabase() {
  let connection;

  try {
    console.log('🔄 正在连接 MySQL...');

    // 从环境变量读取数据库配置
    const dbConfig = {
      host: process.env.DB_HOST || 'localhost',
      port: process.env.DB_PORT || 3306,
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      multipleStatements: true,
      charset: 'utf8mb4'
    };

    // 首先连接到 MySQL（不指定数据库）
    connection = await mysql.createConnection(dbConfig);

    console.log('✅ MySQL 连接成功');

    // 读取 SQL 文件
    const schemaPath = path.join(__dirname, 'database', 'schema.sql');
    console.log(`📄 读取 SQL 文件: ${schemaPath}`);

    let schema = fs.readFileSync(schemaPath, 'utf8');
    // 移除 BOM 标记
    if (schema.charCodeAt(0) === 0xFEFF) {
      schema = schema.slice(1);
    }

    console.log(`📝 读取了 ${schema.length} 字节的 SQL 内容`);
    console.log(`📝 SQL 内容以下列字符开头: ${JSON.stringify(schema.substring(0, 100))}`);

    // 执行 SQL 脚本
    console.log('⚙️  执行数据库初始化脚本...');

    // 分割 SQL 语句并过滤空语句
    // 注意：这里不能过滤掉注释，因为注释往往在语句中间
    const statements = schema
      .split(';')
      .map(s => s.trim())
      .filter(s => s.length > 0);

    console.log(`🔍 分割后得到 ${schema.split(';').length} 个片段`);
    console.log(`📝 过滤后的语句数: ${statements.length}`);

    console.log(`📋 共有 ${statements.length} 条 SQL 语句要执行`);

    for (let i = 0; i < statements.length; i++) {
      const statement = statements[i];
      if (statement.length > 0) {
        try {
          const preview = statement.substring(0, 60).replace(/\n/g, ' ');
          console.log(`  [${i + 1}/${statements.length}] ${preview}...`);
          await connection.query(statement);
        } catch (err) {
          // 忽略 "Query was empty" 错误
          if (err.message !== 'Query was empty') {
            throw err;
          }
        }
      }
    }

    console.log('✅ 数据库初始化成功！');

    // 验证
    const dbName = process.env.DB_NAME || 'SoftwareManagementExp';
    const [rows] = await connection.query(`
      SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?
    `, [dbName]);

    console.log('\n📊 创建的表:');
    rows.forEach(row => console.log('  ✓', row.TABLE_NAME));

    const [stats] = await connection.query(`
      SELECT '用户表' as 表名, COUNT(*) as 记录数 FROM ${dbName}.users
      UNION ALL
      SELECT '请假申请表', COUNT(*) FROM ${dbName}.leaves
      UNION ALL
      SELECT '审批步骤表', COUNT(*) FROM ${dbName}.approval_steps
    `);

    console.log('\n📊 数据统计:');
    console.table(stats);

    process.exit(0);
  } catch (error) {
    console.error('❌ 初始化失败:', error.message);
    process.exit(1);
  } finally {
    if (connection) await connection.end();
  }
}

initDatabase();
